package com.system.Daos;
import com.system.Models.Studentscore;
import javax.swing.table.DefaultTableModel;
import java.sql.*;

public class StudentScoreDao {
    Studentscore studentscore;

    public StudentScoreDao(){

    };

    public StudentScoreDao(Studentscore studentscore) {

        this.studentscore = studentscore;

    }

    private DefaultTableModel getTableModel(ResultSet resultSet) throws SQLException {

        // 获取结果集的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();

        // 获取列数
        int columnCount = metaData.getColumnCount();

        // 创建表格模型
        DefaultTableModel model = new DefaultTableModel(){
            @Override
            public boolean isCellEditable(int row, int column) {
                return false; // 禁止所有单元格的编辑
            }
        };

        // 添加列名
        for (int column = 1; column <= columnCount; column++) {
            model.addColumn(metaData.getColumnName(column));
        }

        // 添加行数据
        while (resultSet.next()) {
            Object[] row = new Object[columnCount];
            for (int column = 1; column <= columnCount; column++) {
                if(metaData.getColumnName(column).equals("id") || Double.parseDouble(resultSet.getObject(column).toString())>0 ){
                    row[column - 1] = resultSet.getObject(column);
                }else{
                    row[column - 1] = null;
                }
            }
            model.addRow(row);
        }

        return model;

    }

    public DefaultTableModel getAllTableModel() throws SQLException {

        Connection con = new MySQLExample().getCon();

        String sql = "SELECT * FROM studentscore"; // 查询语句

        Statement statement = con.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);

        DefaultTableModel model = this.getTableModel(resultSet);

        con.close();
        statement.close();
        resultSet.close();

        return model;

    }

    public boolean addStudentscore() throws SQLException {
        boolean flag = false;
        String id = this.studentscore.getId();
        Double math = this.studentscore.getMath();
        Double english = this.studentscore.getEnglish();

        Connection con = new MySQLExample().getCon();

        String sql = "INSERT INTO `studentscore` (`id`,`math`, `english`) VALUES ('" + id + "', " + math + "," + english + ");";

        // 创建Statement对象
        Statement stmt = con.createStatement();

        int rowsAffected = stmt.executeUpdate(sql);

        if (rowsAffected == 1) {
            flag = true;
        }

        // 关闭连接、语句和结果集
        stmt.close();
        con.close();

        return flag;
    }

    public boolean deleteStudentscore() throws SQLException {
        boolean flag = false;
        String id = this.studentscore.getId();

        Connection con = new MySQLExample().getCon();


        String sql = "DELETE FROM studentscore WHERE `id` = '" + id + "'";

        // 创建Statement对象
        Statement stmt = con.createStatement();

        int rowsAffected = stmt.executeUpdate(sql);

        if (rowsAffected == 1) {
            flag = true;
        }

        // 关闭连接、语句和结果集
        stmt.close();
        con.close();

        return flag;
    }

    public boolean alterStudentscore() throws SQLException {

        boolean f = false;
        Connection con = new MySQLExample().getCon();
        String id = this.studentscore.getId();
        Double math = this.studentscore.getMath();
        Double english = this.studentscore.getEnglish();
        Connection conn = new MySQLExample().getCon();

        String sql = "UPDATE studentscore SET math = "+ math +", english = " + english + " WHERE id = '"+ id+"'";
        // 创建Statement对象
        Statement stmt = con.createStatement();

        int rowsAffected = stmt.executeUpdate(sql);

        if (rowsAffected == 1) {
            f = true;
        }

        // 关闭连接
        stmt.close();
        conn.close();

        return f;

    }

    public DefaultTableModel searchStudentscore() throws SQLException {
        Connection con = new MySQLExample().getCon();

        String sql = "SELECT * FROM studentscore WHERE ("+studentscore.getEnglishRange().getMin()+"<english AND english<"+studentscore.getEnglishRange().getMax()+") AND ("+studentscore.getMathRange().getMin()+"<math AND math<"+studentscore.getMathRange().getMax()+") AND id LIKE '%"+studentscore.getId()+"%'";

        Statement statement = con.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);

        DefaultTableModel model = this.getTableModel(resultSet);

        con.close();
        statement.close();
        resultSet.close();

        return model;
    }

}

